﻿Imports MICROFORMA.BEL
Imports MICROFORMA.DIL
Imports Microsoft.Practices.EnterpriseLibrary.Data
Imports System.Data.Common
Imports System.Data

Public Class LoteDA
    Implements LoteDI


    Public Function Add(ByVal _lote_BE As BEL.LoteBE) As String Implements DIL.LoteDI.Add
        Dim db As Database = DatabaseFactory.CreateDatabase()
        Dim DetalleLoteDA = New DetalleLoteDA
        Try
            '_solicitud_BE.Fecha _
            Dim query As String = "insert into lote (IdSolicitud, Nombre, Estado, FechaCreacion, FechaModificacion) " _
                                    & "Values (" & _lote_BE.IdSolicitud & ",'" & _lote_BE.Nombre & "'" _
                                    & ",0,GETDATE(),GETDATE())"
            Dim o_dbCommand As DbCommand = db.GetSqlStringCommand(query)
            db.ExecuteNonQuery(o_dbCommand)

            Dim idLote As Integer = getIdLastRecord()

            If idLote > 0 Then
                For Each _DetalleLoteBE In _lote_BE.DetalleLoteBE
                    _DetalleLoteBE.IdLote = idLote
                    DetalleLoteDA.Add(_DetalleLoteBE)
                Next
            End If

            

            Return "OK"

        Catch ex As Exception

            Return ex.Message.ToString

        End Try
    End Function

    Public Function Delete(ByVal Id As Integer) As String Implements DIL.LoteDI.Delete
        Dim db As Database = DatabaseFactory.CreateDatabase()

        Try
            Dim query As String = "Update Lote Set estado=1" _
                                 & ", FechaModificacion=" & "getDate()" _
                                 & " where IdLote=" & Id


            Dim o_dbCommand As DbCommand = db.GetSqlStringCommand(query)


            db.ExecuteNonQuery(o_dbCommand)


            Return "OK"

        Catch ex As Exception

            Return ex.Message.ToString

        End Try
    End Function

    Public Function getIdLastRecord() As Integer Implements DIL.LoteDI.getIdLastRecord

        Dim idLote As Integer = 0

        Dim db As Database = DatabaseFactory.CreateDatabase

        Dim query As String = "select max(IdLote) from Lote"

        Dim o_dbCommand As DbCommand = db.GetSqlStringCommand(query)

        Dim o_Cnx As DbConnection = db.CreateConnection()

        o_Cnx.Open()

        Dim o_Transaction As DbTransaction = o_Cnx.BeginTransaction

        Try
            idLote = Convert.ToInt32(db.ExecuteScalar(o_dbCommand))
        Catch ex As Exception
            o_Transaction.Rollback()
            Return idLote
        Finally
            o_Cnx.Close()
        End Try

        Return idLote


    End Function

    Public Function GetList() As System.Data.DataTable Implements DIL.LoteDI.GetList
        Dim db As Database = DatabaseFactory.CreateDatabase()

        Dim query As String = "select idlote, idsolicitud, nombre, estado, fechacreacion, (select nombre from clientes where idcliente in (select idcliente from solicitud s where s.idsolicitud = l.idsolicitud)) Cliente " & _
                                "from lote l  " & _
                                "where estado = 0			"

        Dim dbCommand As DbCommand = db.GetSqlStringCommand(query)

        Dim productsDataSet As DataSet = Nothing

        productsDataSet = db.ExecuteDataSet(dbCommand)

        Return productsDataSet.Tables(0)

    End Function

    Public Function GetListToScan() As System.Data.DataTable Implements DIL.LoteDI.GetListToScan
        Dim db As Database = DatabaseFactory.CreateDatabase()

        Dim query As String = "SELECT IdLote,Nombre," _
                        & " (SELECT SUM(CANTIDADDOCUMENTOS) FROM DetalleLote WHERE DetalleLote.IDLOTE=L.IDLOTE and Estado='0') as 'DOCUMENTOS'," _
                        & " (SELECT SUM(CantidadFolios) FROM DetalleLote WHERE DetalleLote.IDLOTE=L.IDLOTE and Estado='0')  as 'FOLIOS'," _
                        & " (select nombre from clientes where idcliente in (select idcliente from solicitud s where s.idsolicitud = L.idsolicitud)) Cliente " _
                        & " FROM Lote L " _
                        & " WHERE Estado='0'"

        Dim dbCommand As DbCommand = db.GetSqlStringCommand(query)

        Dim productsDataSet As DataSet = Nothing

        productsDataSet = db.ExecuteDataSet(dbCommand)

        Return productsDataSet.Tables(0)

    End Function


End Class
